﻿using Model;
using MySqlConnector;
using System.Data;

namespace DAL
{
    /// <summary>
    /// 社团用户数据访问类，继承基类BaseDAL
    /// </summary>
    public class ClubUserDAL : BaseDAL<ClubUser>
    {
        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns>数据表</returns>
        public DataTable GetAllUsers()
        {
            //定义查询语句
            string sql = "SELECT Id, UserName, Password, Role FROM clubuser WHERE DeleteFlag = 0";
            //执行查询并返回查询结果（数据表）
            return Query(sql);
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns>用户对象集合</returns>
        public List<ClubUser> GetAllUserList()
        {
            //定义查询语句
            string sql = "SELECT Id, UserName, Password, Role FROM clubuser WHERE DeleteFlag = 0";
            //执行查询并获取查询结果：数据表
            DataTable dt = Query(sql);
            //定义一个CLubUser集合
            List<ClubUser> users = new List<ClubUser>();
            //遍历数据表，
            foreach (DataRow dr in dt.Rows)
            {
                ClubUser user = new ClubUser();
                //把每一行转换为一个CLubUser实体
                user = ConverToModel(dr);
                //把转换的ClubUser实体添加到集合中
                users.Add(user);
            }
            return users;
        }

        /// <summary>
        /// 根据Id获取用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ClubUser? GetUserById(int id)
        {
            //定义查询语句
            string sql = "SELECT Id, UserName, Password, Role FROM clubuser WHERE Id = @Id";
            //定义参数
            MySqlParameter p = new MySqlParameter("@Id", MySqlDbType.Int32) { Value = id };
            //执行查询并获取查询结果：数据表
            DataTable dt = Query(sql, p);
            //判断表中是否有数据，若有就把数据表的第一行转换为ClubUser实体并返回这个实体，若没有就返回null
            return dt.Rows.Count > 0 ? ConverToModel(dt.Rows[0]) : null;
        }

        /// <summary>
        /// 把表的行转换为用户实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public override ClubUser ConverToModel(DataRow dr)
        {
            return new ClubUser
            {
                Id = (int)dr["Id"],
                UserName = dr["UserName"].ToString(),
                Password = dr["Password"].ToString(),
                Role = dr["Role"].ToString()
            };
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="clubUser"></param>
        /// <returns></returns>
        public int UpdateClubUser(ClubUser clubUser)
        {
            //1，定义查询语句
            string sql = "UPDATE ClubUser SET UserName = @UserName, Password = @Password, Role = @Role WHERE Id = @Id";
            //2。处理添加参数
            MySqlParameter[] ps = [
                new MySqlParameter("@UserName",MySqlDbType.VarChar, 50){Value=clubUser.UserName},
                new MySqlParameter("@Password",MySqlDbType.VarChar, 50){ Value=clubUser.Password},
                new MySqlParameter("@Role",MySqlDbType.VarChar,100){ Value=clubUser.Role},
                new MySqlParameter("@Id",MySqlDbType.Int64){ Value=clubUser.Id}
            ];
            //3.执行查询，并返回受影响的行数（执行成功，返回1）
            return Update(sql, ps);
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="clubUser"></param>
        /// <returns></returns>
        public int AddClubUser(ClubUser clubUser)
        {
            string sql =
                "INSERT INTO ClubUser(UserName, Password, Role) " +
                "VALUES (@UserName, @Password, @Role)";
            MySqlParameter[] ps = {
                new MySqlParameter("@UserName",MySqlDbType.VarChar,50){Value=clubUser.UserName},
                new MySqlParameter("@Password",MySqlDbType.VarChar,50){Value=clubUser.Password},
                new MySqlParameter("@Role",MySqlDbType.VarChar,100){Value=clubUser.Role}
            };
            return Add(sql, ps);
        }

        /// <summary>
        /// 逻辑删除系统用户（更新DeleteFlag为1）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteClubUser(int id)
        {
            string sql =
                "UPDATE ClubUser SET DeleteFlag = 1 WHERE Id = @id";
            MySqlParameter p = new("@id", MySqlDbType.Int32) { Value=id};
            return Update(sql, p);
        }
    }
}
